package com.ruoyi.kycinfo.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Set;


import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.kycinfo.domain.*;
import com.ruoyi.kycinfo.service.*;
import com.ruoyi.system.service.ISysUserService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.kycinfo.mapper.KycUserInfoMapper;
import org.springframework.transaction.annotation.Transactional;

/**
 * kyc客户 Service业务层处理
 *
 * @author ruoyi
 * @date 2023-10-13
 */
@Service
public class KycUserInfoServiceImpl implements IKycUserInfoService {

    @Autowired
    private ISysUserService sysUserService;

    @Autowired
    private KycUserInfoMapper kycUserInfoMapper;

    @Autowired
    private IKycFamilyService kycFamilyService;

    @Autowired
    private IKycWorkService kycWorkService;

    @Autowired
    private IKycProductService kycProductService;

    @Autowired
    private IKycPotentialService kycPotentialService;

    @Autowired
    private IKycContactService kycContactService;

    /**
     * 查询【请填写功能名称】
     *
     * @param userInfoId 【请填写功能名称】主键
     * @return 【请填写功能名称】
     */
    @Override
    public KycUserInfo selectKycUserInfoByUserInfoId(Long userInfoId) {
        return kycUserInfoMapper.selectKycUserInfoByUserInfoId(userInfoId);
    }

    @Override
    public List<KycUserInfo> selectKycUserInfoListByUserId(Long userid) {
        return kycUserInfoMapper.selectKycUserInfoListByUserId(userid);
    }


    /**
     * 查询【请填写功能名称】列表
     *
     * @param kycUserInfo 【请填写功能名称】
     * @return 【请填写功能名称】
     */
    @Override
    public List<KycUserInfo> selectKycUserInfoList(KycUserInfo kycUserInfo) {
        return kycUserInfoMapper.selectKycUserInfoList(kycUserInfo);
    }

    /**
     * 新增【请填写功能名称】
     *
     * @param kycUserInfo 【请填写功能名称】
     * @return 结果
     */
    @Override
    public int insertKycUserInfo(KycUserInfo kycUserInfo) {
        kycUserInfo.setCreateTime(DateUtils.getNowDate());
        return kycUserInfoMapper.insertKycUserInfo(kycUserInfo);
    }

    /**
     * 修改【请填写功能名称】
     *
     * @param kycUserInfo 【请填写功能名称】
     * @return 结果
     */
    @Override
    public int updateKycUserInfo(KycUserInfo kycUserInfo) {
        kycUserInfo.setUpdateTime(DateUtils.getNowDate());
        return kycUserInfoMapper.updateKycUserInfo(kycUserInfo);
    }

    /**
     * 批量删除【请填写功能名称】
     *
     * @param userInfoIds 需要删除的【请填写功能名称】主键
     * @return 结果
     */
    @Override
    public int deleteKycUserInfoByUserInfoIds(Long[] userInfoIds) {
        return kycUserInfoMapper.deleteKycUserInfoByUserInfoIds(userInfoIds);
    }

    /**
     * 删除【请填写功能名称】信息
     *
     * @param userInfoId 【请填写功能名称】主键
     * @return 结果
     */
    @Override
    public int deleteKycUserInfoByUserInfoId(Long userInfoId) {
        return kycUserInfoMapper.deleteKycUserInfoByUserInfoId(userInfoId);
    }

    /*
     * 读取excel文件，导入kyc客户信息数据到数据库
     * */
    @Transactional
    @Override
    public String importKycInfo(Long userId, boolean updateSupport, InputStream inputStream) throws IOException, ParseException {
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet页
        int rows = sheet.getLastRowNum();
        //int columns = sheet.getRow(0).getLastCellNum();
        long userInfoId = 0L;

        SimpleDateFormat dateFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);

        KycUserInfo kycUserInfo = new KycUserInfo();
        KycWork kycWork = new KycWork();
        KycProduct kycProduct = new KycProduct();

        List<Long> errUserInfoIdList = new ArrayList<>();
        List<Long> repeatUserInfoIdList = new ArrayList<>();

        int lastRow = 0;    //合并单元格的最后一行

        // 之前是0，修改为了1，因为添加了用户编号字段
        int startColIndex = 1;
        // 这里的 rows 是从0开始的，所以要取等于号
        for (int i = 1; i <= rows; i++) {
            Row row = sheet.getRow(i);
            if (row != null && !isEmptyRow(row)) {
                Cell cell = row.getCell(0);
                CellAddress address = cell.getAddress();

                CellRangeAddress mergedRegion = getMergedRegion(sheet, address);
                if (mergedRegion != null) {
                    // cell是合并单元格
                    //firstRow = mergedRegion.getFirstRow(); //合并单元格的第一行
                    lastRow = mergedRegion.getLastRow();   //合并单元格的最后一行

//                int firstColumn = mergedRegion.getFirstColumn();
//                Cell mergedCell = sheet.getRow(firstRow).getCell(firstColumn);
//                System.out.println("Merged Cell Value: " + getValue(mergedCell));

                    // 合并单元格内容为空，说明文件读取完毕，结束循环
                    String s = getValue(cell);
                    if (StringUtils.isEmpty(s)) {
                        break;
                    }
                } else {
                    // 不是合并单元格
//                    System.out.println("Cell Value: " + getValue(cell));
                    return "文件格式不是合并单元格，文件导入失败，请使用模板文件中的格式";
                }

                // 将userId修改为上传文件的第二列
                kycUserInfo.setUserId(getLongValue(row.getCell(startColIndex)));
                kycUserInfo.setName(getValue(row.getCell(startColIndex + 1)));
                // 设置客户号，输入不合法时忽略掉本行数据
                Long cellValue = getLongValue(row.getCell(startColIndex + 2));
                if (cellValue > 0)
                    kycUserInfo.setUserInfoId(cellValue);
                else {
                    errUserInfoIdList.add(cellValue);
                    i = lastRow;
                    continue;
                }
                kycUserInfo.setGender(getValue(row.getCell(startColIndex + 3)));
                kycUserInfo.setAge(getIntValue(row.getCell(startColIndex + 4)));
                kycUserInfo.setWeChat(getValue(row.getCell(startColIndex + 5)));
                kycUserInfo.setContactTime(getValue(row.getCell(startColIndex + 6)));
                kycUserInfo.setNativePlace(getValue(row.getCell(startColIndex + 7)));
                kycUserInfo.setClienType(getValue(row.getCell(startColIndex + 8)));
                kycUserInfo.setMarriage(getValue(row.getCell(startColIndex + 9)));
                kycUserInfo.setEducation(getValue(row.getCell(startColIndex + 10)));
                kycUserInfo.setHouseCount(getIntValue(row.getCell(startColIndex + 11)));
                kycUserInfo.setHouseSource(getValue(row.getCell(startColIndex + 12)));
                kycUserInfo.setHosueType1(getValue(row.getCell(startColIndex + 13)));
                kycUserInfo.setHouseAddress1(getValue(row.getCell(startColIndex + 14)));
                kycUserInfo.setHosueType2(getValue(row.getCell(startColIndex + 15)));
                kycUserInfo.setHouseAddress2(getValue(row.getCell(startColIndex + 16)));
                kycUserInfo.setCarCount(getIntValue(row.getCell(startColIndex + 17)));
                kycUserInfo.setCarSource(getValue(row.getCell(startColIndex + 18)));
                kycUserInfo.setCarType(getValue(row.getCell(startColIndex + 19)));
                kycUserInfo.setCarBrank(getValue(row.getCell(startColIndex + 20)));
                kycUserInfo.setMedicalHistory(getValue(row.getCell(startColIndex + 21)));
                kycUserInfo.setDietaryHabit(getValue(row.getCell(startColIndex + 22)));
                kycUserInfo.setExerciseMethod(getValue(row.getCell(startColIndex + 23)));
                kycUserInfo.setHobby(getValue(row.getCell(startColIndex + 24)));
                kycUserInfo.setLivingNeed(getValue(row.getCell(startColIndex + 25)));
                kycUserInfo.setConsumeHabit(getValue(row.getCell(startColIndex + 26)));
                kycUserInfo.setTemper(getValue(row.getCell(startColIndex + 27)));
                kycUserInfo.setTripMode(getValue(row.getCell(startColIndex + 28)));
                kycUserInfo.setBelief(getValue(row.getCell(startColIndex + 29)));

//                System.out.println(kycUserInfo);
                userInfoId = kycUserInfo.getUserInfoId();
                KycUserInfo findUserInfo = this.selectKycUserInfoByUserInfoId(userInfoId);
                // 如果客户信息已经存在
                if (findUserInfo != null) {
                    // 如果更新客户信息
                    if (updateSupport) {
                        // 更新kyc客户信息
                        kycUserInfoMapper.updateKycUserInfo(kycUserInfo);

                        // 删除该客户之前的其他信息，包括家庭信息，工作信息，潜力洞察，联络情况
                        kycFamilyService.deleteKycFamilyByUserInfoId(userInfoId);
                        kycWorkService.deleteKycWorkByUserInfoId(userInfoId);
                        kycProductService.deleteKycProductByUserInfoId(userInfoId);
//                        kycPotentialService.deleteKycPotentialByUserInfoId(userInfoId);
//                        kycContactService.deleteKycContactByUserInfoId(userInfoId);
//                        System.out.println("更新客户号'" + userInfoId + "'客户其他信息");
                    } else {
                        // 不更新数据，则忽略本条数据
                        repeatUserInfoIdList.add(userInfoId);
                        i = lastRow;
                        continue;
                    }
                } else {
                    this.insertKycUserInfo(kycUserInfo);
//                    System.out.println("新增客户号'" + userInfoId + "'客户信息成功！！！！");
                }

                // 处理KycFamily对象 可能有0-5个
                for (int j = i; j <= lastRow; j++) {
                    Row familyRow = sheet.getRow(j);
                    if (StringUtils.isEmpty(getValue(familyRow.getCell(startColIndex + 30)))) {
                        break;
                    }
                    KycFamily kycFamily = new KycFamily();

                    kycFamily.setUserInfoId(userInfoId);
                    kycFamily.setFamilyName(getValue(familyRow.getCell(startColIndex + 30)));
                    kycFamily.setRelationship(getValue(familyRow.getCell(startColIndex + 31)));
                    kycFamily.setOccupation(getValue(familyRow.getCell(startColIndex + 32)));
                    kycFamily.setFamilyPost(getValue(familyRow.getCell(startColIndex + 33)));
                    kycFamily.setMyClient(getValue(familyRow.getCell(startColIndex + 34)));
                    kycFamily.setFamilyBirth(dateFormat.parse(getValue(familyRow.getCell(startColIndex + 35))));
                    kycFamily.setFamilyHobby(getValue(familyRow.getCell(startColIndex + 36)));
                    kycFamily.setFamilyRemark(getValue(familyRow.getCell(startColIndex + 37)));

//                    System.out.println(kycFamily);
                    kycFamilyService.insertKycFamily(kycFamily);
//                    System.out.println("插入客户家庭信息ok~~");
                }

                kycWork.setUserInfoId(userInfoId);
                kycWork.setCompanyName(getValue(row.getCell(startColIndex + 38)));
                kycWork.setAddress(getValue(row.getCell(startColIndex + 39)));
                kycWork.setEvection(getValue(row.getCell(startColIndex + 40)));
                kycWork.setEvectionPlace(getValue(row.getCell(startColIndex + 41)));
                kycWork.setDepartment(getValue(row.getCell(startColIndex + 42)));
                kycWork.setCompanyPost(getValue(row.getCell(startColIndex + 43)));

                kycWork.setLevel(getValue(row.getCell(startColIndex + 44)));
                kycWork.setPassport(getValue(row.getCell(startColIndex + 45)));
                kycWork.setHaveBeen(getValue(row.getCell(startColIndex + 46)));
                kycWork.setCompanyNature(getValue(row.getCell(startColIndex + 47)));
                kycWork.setCountry(getValue(row.getCell(startColIndex + 48)));
                kycWork.setWorkExperience(getValue(row.getCell(startColIndex + 49)));
                kycWork.setLife(getValue(row.getCell(startColIndex + 50)));
                kycWork.setCompanyRemark(getValue(row.getCell(startColIndex + 51)));

//                System.out.println(kycWork);
                kycWorkService.insertKycWork(kycWork);
//                System.out.println("插入客户工作信息ok~~");

                kycProduct.setUserInfoId(userInfoId);
                kycProduct.setRisk(getValue(row.getCell(startColIndex + 52)));
                kycProduct.setKeyProduct(getValue(row.getCell(startColIndex + 53)));
                kycProduct.setMyLoan(getValue(row.getCell(startColIndex + 54)));
                kycProduct.setMyLoanReason(getValue(row.getCell(startColIndex + 55)));
                kycProduct.setOtherLoan(getValue(row.getCell(startColIndex + 56)));
                kycProduct.setOtherLoanReason(getValue(row.getCell(startColIndex + 57)));
                kycProduct.setCreditCardCount(getIntValue(row.getCell(startColIndex + 58)));
                kycProduct.setCreditCardType(getValue(row.getCell(startColIndex + 59)));
                kycProduct.setSituation(getValue(row.getCell(startColIndex + 60)));
                kycProduct.setFocus(getValue(row.getCell(startColIndex + 61)));
                kycProduct.setDebitCardType(getValue(row.getCell(startColIndex + 62)));
                kycProduct.setOtherBank(getValue(row.getCell(startColIndex + 63)));
                kycProduct.setLoanRelevant(getValue(row.getCell(startColIndex + 64)));
                kycProduct.setBind(getValue(row.getCell(startColIndex + 65)));
                kycProduct.setBindInstitution(getValue(row.getCell(startColIndex + 66)));
                kycProduct.setDepositType(getValue(row.getCell(startColIndex + 67)));
                kycProduct.setMoneyManage(getValue(row.getCell(startColIndex + 68)));
                kycProduct.setTrust(getValue(row.getCell(startColIndex + 69)));
                kycProduct.setFund(getValue(row.getCell(startColIndex + 70)));
                kycProduct.setInsurance(getValue(row.getCell(startColIndex + 71)));
                kycProduct.setInsuranceCompany(getValue(row.getCell(startColIndex + 72)));
                kycProduct.setNobleMetal(getValue(row.getCell(startColIndex + 73)));
                kycProduct.setActivity(getValue(row.getCell(startColIndex + 74)));
                kycProduct.setProductRemark(getValue(row.getCell(startColIndex + 75)));

//                System.out.println(kycProduct);
                kycProductService.insertKycProduct(kycProduct);
//                System.out.println("插入客户金融产品信息ok~~");
                i = lastRow;

                // 处理kycPotential对象 可能有0-5个
               /* for (int j = i; j <= lastRow; j++) {
                    Row potentialRow = sheet.getRow(j);
                    if (StringUtils.isEmpty(getValue(potentialRow.getCell(startColIndex + 76)))) {
                        break;
                    }
                    KycPotential kycPotential = new KycPotential();

                    kycPotential.setUserInfoId(userInfoId);
                    kycPotential.setPotentialName(getValue(potentialRow.getCell(startColIndex + 76)));
                    kycPotential.setRelationship(getValue(potentialRow.getCell(startColIndex + 77)));
                    kycPotential.setIsMyClient(getValue(potentialRow.getCell(startColIndex + 78)));
                    kycPotential.setPreference(getValue(potentialRow.getCell(startColIndex + 79)));

//                    System.out.println(kycPotential);
                    kycPotentialService.insertKycPotential(kycPotential);
//                    System.out.println("插入客户潜力洞察信息ok~~");
                }*/

                // 处理kycContact对象 可能有0-3个
               /* for (int j = i; j <= lastRow; j++) {
                    Row contactRow = sheet.getRow(j);
                    if (StringUtils.isEmpty(getValue(contactRow.getCell(startColIndex + 80)))) {
                        break;
                    }
                    KycContact kycContact = new KycContact();

                    kycContact.setUserInfoId(userInfoId);
                    kycContact.setDate(dateFormat.parse(getValue(contactRow.getCell(startColIndex + 80))));
                    kycContact.setPhone(getValue(contactRow.getCell(startColIndex + 81)));
                    kycContact.setContactStatus(getValue(contactRow.getCell(startColIndex + 82)));
                    kycContact.setRecommend(getValue(contactRow.getCell(startColIndex + 83)));
                    kycContact.setResult(getValue(contactRow.getCell(startColIndex + 84)));
                    kycContact.setFollowDate(dateFormat.parse(getValue(contactRow.getCell(startColIndex + 85))));
                    kycContact.setFollowWay(getValue(contactRow.getCell(startColIndex + 86)));
                    kycContact.setFollowIntention(getValue(contactRow.getCell(startColIndex + 87)));
                    kycContact.setContactRemark(getValue(contactRow.getCell(startColIndex + 88)));

//                    System.out.println(kycContact);
                    kycContactService.insertKycContact(kycContact);
//                    System.out.println("插入客户联络信息ok~~");
                }*/
//            System.out.println("读取第" + i + "行数据成功！");
//                i = lastRow;
            }
        }
        // 关闭输入流
        inputStream.close();
        String ans = "文件导入成功";

        if (errUserInfoIdList.size() != 0) {
            ans += "<br>部分客户号输入不正确，请您检查客户号:" + errUserInfoIdList;
        }
        if (repeatUserInfoIdList.size() != 0) {
            ans += "<br>客户号:"+repeatUserInfoIdList+"信息已存在，已自动忽略该客户信息的导入<br>如需更新客户信息，请选择更新已经存在的用户数据";
        }
        return ans;
    }


    // 导入KYC客户所属客户经理变更信息
    @Transactional
    @Override
    public String importKycInfoChange(InputStream inputStream) throws IOException, ParseException {
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet页
        // 这里的 rows 是从0开始的
        int rows = sheet.getLastRowNum();

        List<Long> errUserInfoIdList = new ArrayList<>();
        List<SysUser> sysUsers = sysUserService.selectUserList(new SysUser());
        List<Long> sysUserIds = new ArrayList<>();
        for (SysUser sysUser : sysUsers) {
            sysUserIds.add(sysUser.getUserId());
        }
        sysUserIds.add(SecurityUtils.getUserId());

        // 这里的 rows 是从0开始的，所以要取等于号
        for (int i = 1; i <= rows; i++) {
            Row row = sheet.getRow(i);
            if (row != null && !isEmptyRow(row)) {
                Long userInfoId = getLongValue(row.getCell(0));
                // 对两个用户编号进行数据校验，判断上传文件的用户，是否有访问两个用户编号的权限
                Long oldUserId = getLongValue(row.getCell(1));
                Long newUserId = getLongValue(row.getCell(2));
                if (!(sysUserIds.contains(oldUserId) && sysUserIds.contains(newUserId))) {
                    errUserInfoIdList.add(userInfoId);
                    continue;
                }

                KycUserInfo findKycUserInfo = selectKycUserInfoByUserInfoId(userInfoId);
                if (findKycUserInfo == null || findKycUserInfo.getUserId() != oldUserId) {
                    // 输入信息有误，pass该条数据，读取下一条数据
                    errUserInfoIdList.add(userInfoId);
                } else {
                    // 根据客户号查询到客户信息，开始变更客户所属的客户经理
                    findKycUserInfo.setUserId(newUserId);
                    updateKycUserInfo(findKycUserInfo);
                }
            }
        }
        // 关闭输入流
        inputStream.close();

        if (errUserInfoIdList.size() == 0) {
            return "文件导入成功";
        } else {
            return "文件导入成功！部分客户号信息有误，请您检查客户号:" + errUserInfoIdList;
        }
    }


    // 查询user_id在指定user_id列表中的KycUserInfo列表
    @Override
    public List<KycUserInfo> selectKycUserInfoListByUserIdList(List<Long> userIdList) {
        return kycUserInfoMapper.selectKycUserInfoListByUserIdList(userIdList);
    }


    public List<KycUserInfo> selectKycUserInfoListByUserInfoIdList(List<Long> userInfoIdList) {
        return kycUserInfoMapper.selectKycUserInfoListByUserInfoIdList(userInfoIdList);
    }


    // 获取单元格值
    public static String getValue(Cell cell) {
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return Double.toString(cell.getNumericCellValue());
                }
            default:
                return "";
        }
    }

    // 获取合并单元格信息
    public static CellRangeAddress getMergedRegion(Sheet sheet, CellAddress address) {
        for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
            if (mergedRegion.isInRange(address.getRow(), address.getColumn())) {
                return mergedRegion;
            }
        }
        return null;
    }

    private boolean isEmptyRow(Row row) {
        for (Cell cell : row) {
            if (cell.getCellType() != CellType.BLANK) {
                return false; // 存在非空单元格，不是空行
            }
        }
        return true; // 所有单元格都是空单元格，是空行
    }

    // 读取单元格内容，返回long类型的数据
    public static Long getLongValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return -1L;
        }
        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC)
            return (long) cell.getNumericCellValue();
        else if (cellType == CellType.STRING) {
            try {
                return Long.parseLong(cell.getStringCellValue());
            } catch (NumberFormatException e) {
                return -1L;
            }
        }
        return -1L;
    }

    // 读取单元格内容，返回int类型的数据
    public static int getIntValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return -1;
        }
        CellType cellType = cell.getCellType();

        if (cellType == CellType.NUMERIC)
            return (int) cell.getNumericCellValue();
        else if (cellType == CellType.STRING) {
            try {
                return Integer.parseInt(cell.getStringCellValue());
            } catch (NumberFormatException e) {
                return -1;
            }
        }
        return -1;
    }
//    public static String getIntValue(Cell cell) {//整形样式的字符串
//        CellType cellType = cell.getCellType();
//        switch (cellType) {
//            case STRING:
//                return cell.getStringCellValue();
//            case NUMERIC:
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    return cell.getDateCellValue().toString();
//                } else {
//                    return Double.toString(cell.getNumericCellValue());
//                }
//            default:
//                return "";
//        }
//    }

}
